import os
import psycopg2
import psycopg2.extras
import sys
from shapely.wkt import dumps, loads
import constant
import datetime
import glob


aqstime_query="select aqstime from {0} where filename like '%{1}%'"
mod04_query="SELECT distinct max(aqstime) as aqstime,left(max(filepath),56) as filepath,max(type) as type,max(collection) as collection,left(filename,17) as ju_date from {0} group by ju_date order by ju_date asc"

insert_query="INSERT INTO {0}(aqstime, rasref3, filename, filepath, projection,type, collection) VALUES ('{1}'::timestamp, '{2}'::raster, '{3}', '{4}', 1, 1, 6)" 
insert_daily_query="INSERT INTO {0}(aqstime, rasref10, filename, filepath, projection,type, collection) VALUES ('{1}'::timestamp, '{2}'::raster, '{3}', '{4}', {5}, {6}, {7})" 

update_query="UPDATE {0} SET rasref6='{1}'::raster,rasref10='{2}'::raster WHERE filename ='{3}'"

		
def insertMOD07(query):
	org_folder = "SatResampMOD07"
	resample_folder= "SatResampMOD07_Daily"
	temp3_format = "_T_3km.tif"
	press3_format = "_P_3km.tif"
	temp6_format = "_T_6km.tif"
	press6_format = "_P_6km.tif"
	temp10_format = "_T_10km.tif"
	press10_format = "_P_10km.tif"
	
	
	temp_command = "raster2pgsql -a -f rasref -F {1} apom.satresampmod07temperature_daily"
	press_command = "raster2pgsql -a -f rasref -F {1} apom.satresampmod07pressure_daily "
	
	
	con = None
	try:
		con = psycopg2.connect(host=constant.host, database = constant.database, user= constant.user, password=constant.password) 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		
		#cur.execute("SELECT filename, path, ST_AsText(corner) as corner FROM satorgmod04")
		cur.execute(query)
		rows = cur.fetchall()
		
		for row in rows:
			aqstime=row["aqstime"]
			type = row["type"]
			collection = row["collection"]
			path = row["filepath"].strip()
			filename = row["ju_date"].strip()
			
			path = os.path.join(constant.folder_path, path)	
			resamp_path=path.replace(org_folder, resample_folder)
			
			
			temp3_file = os.path.join(resamp_path, filename + temp3_format)
			temp6_file = os.path.join(resamp_path, filename + temp6_format)
			temp10_file = os.path.join(resamp_path, filename + temp10_format)
			
			press3_file = os.path.join(resamp_path, filename + press3_format)
			press6_file = os.path.join(resamp_path, filename + press6_format)
			press10_file = os.path.join(resamp_path, filename + press10_format)

			# apom.satresampmod04: table name
			# -F: tu dong insert ten file thanh 1 cot
			# raster2pgsql/{1}.sql: duong dan file sql
			if os.path.isfile(temp3_file):
				temp3_script=os.popen(temp_command.format("rasref3",temp3_file, filename)).read()
				first_index = temp3_script.find("('")
				last_index = temp3_script.find("':")
				temp3_ref=temp3_script[first_index+2:last_index]
			
				temp6_script=os.popen(temp_command.format("rasref6",temp6_file, filename)).read()
				first_index = temp6_script.find("('")
				last_index = temp6_script.find("':")
				temp6_ref=temp6_script[first_index+2:last_index]
				
				temp10_script=os.popen(temp_command.format("rasref10",temp10_file, filename)).read()
				first_index = temp10_script.find("('")
				last_index = temp10_script.find("':")
				temp10_ref=temp10_script[first_index+2:last_index]
				
				a = insert_query.format("apom.satresampmod07temperature_daily",aqstime,temp3_ref,filename,resamp_path)
								
				cur.execute(a)
				cur.execute('COMMIT')
				b=update_query.format("apom.satresampmod07temperature_daily",temp6_ref,temp10_ref,filename)
				cur.execute(b)
				cur.execute('COMMIT')
				
				
				press3_script=os.popen(press_command.format("rasref3",press3_file, filename)).read()
				first_index = press3_script.find("('")
				last_index = press3_script.find("':")
				press3_ref=press3_script[first_index+2:last_index]
				
				press6_script=os.popen(press_command.format("rasref6",press6_file, filename)).read()
				first_index = press6_script.find("('")
				last_index = press6_script.find("':")
				press6_ref=press6_script[first_index+2:last_index]
				
				press10_script=os.popen(press_command.format("rasref10",press10_file, filename)).read()
				first_index = press10_script.find("('")
				last_index = press10_script.find("':")
				press10_ref=press10_script[first_index+2:last_index]
			
				
				a = insert_query.format("apom.satresampmod07pressure_daily",aqstime,press3_ref,filename,resamp_path)
								
				cur.execute(a)
				cur.execute('COMMIT')
				b=update_query.format("apom.satresampmod07pressure_daily",press6_ref,press10_ref,filename)
				cur.execute(b)
				cur.execute('COMMIT')
			
	

	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()
			
def insertMYD07(query):
	org_folder = "SatResampMYD07"
	resample_folder= "SatResampMYD07_Daily"
	temp3_format = "_T_3km.tif"
	press3_format = "_P_3km.tif"
	temp6_format = "_T_6km.tif"
	press6_format = "_P_6km.tif"
	temp10_format = "_T_10km.tif"
	press10_format = "_P_10km.tif"
	
	
	temp_command = "raster2pgsql -a -f rasref -F {1} apom.satresampmyd07temperature_daily"
	press_command = "raster2pgsql -a -f rasref -F {1} apom.satresampmyd07pressure_daily "
	
	
	con = None
	try:
		con = psycopg2.connect(host=constant.host, database = constant.database, user= constant.user, password=constant.password) 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		
		#cur.execute("SELECT filename, path, ST_AsText(corner) as corner FROM satorgmod04")
		cur.execute(query)
		rows = cur.fetchall()
		
		for row in rows:
			aqstime=row["aqstime"]
			type = row["type"]
			collection = row["collection"]
			path = row["filepath"].strip()
			filename = row["ju_date"].strip()
			
			path = os.path.join(constant.folder_path, path)	
			resamp_path=path.replace(org_folder, resample_folder)
			
			
			temp3_file = os.path.join(resamp_path, filename + temp3_format)
			temp6_file = os.path.join(resamp_path, filename + temp6_format)
			temp10_file = os.path.join(resamp_path, filename + temp10_format)
			
			press3_file = os.path.join(resamp_path, filename + press3_format)
			press6_file = os.path.join(resamp_path, filename + press6_format)
			press10_file = os.path.join(resamp_path, filename + press10_format)

			# apom.satresampmod04: table name
			# -F: tu dong insert ten file thanh 1 cot
			# raster2pgsql/{1}.sql: duong dan file sql
			if os.path.isfile(temp3_file):
				temp3_script=os.popen(temp_command.format("rasref3",temp3_file, filename)).read()
				first_index = temp3_script.find("('")
				last_index = temp3_script.find("':")
				temp3_ref=temp3_script[first_index+2:last_index]
			
				temp6_script=os.popen(temp_command.format("rasref6",temp6_file, filename)).read()
				first_index = temp6_script.find("('")
				last_index = temp6_script.find("':")
				temp6_ref=temp6_script[first_index+2:last_index]
				
				temp10_script=os.popen(temp_command.format("rasref10",temp10_file, filename)).read()
				first_index = temp10_script.find("('")
				last_index = temp10_script.find("':")
				temp10_ref=temp10_script[first_index+2:last_index]
				
				a = insert_query.format("apom.satresampmyd07temperature_daily",aqstime,temp3_ref,filename,resamp_path)
								
				cur.execute(a)
				cur.execute('COMMIT')
				b=update_query.format("apom.satresampmyd07temperature_daily",temp6_ref,temp10_ref,filename)
				cur.execute(b)
				cur.execute('COMMIT')
				
				
				press3_script=os.popen(press_command.format("rasref3",press3_file, filename)).read()
				first_index = press3_script.find("('")
				last_index = press3_script.find("':")
				press3_ref=press3_script[first_index+2:last_index]
				
				press6_script=os.popen(press_command.format("rasref6",press6_file, filename)).read()
				first_index = press6_script.find("('")
				last_index = press6_script.find("':")
				press6_ref=press6_script[first_index+2:last_index]
				
				press10_script=os.popen(press_command.format("rasref10",press10_file, filename)).read()
				first_index = press10_script.find("('")
				last_index = press10_script.find("':")
				press10_ref=press10_script[first_index+2:last_index]
			
				
				a = insert_query.format("apom.satresampmyd07pressure_daily",aqstime,press3_ref,filename,resamp_path)
								
				cur.execute(a)
				cur.execute('COMMIT')
				b=update_query.format("apom.satresampmyd07pressure_daily",press6_ref,press10_ref,filename)
				cur.execute(b)
				cur.execute('COMMIT')
			
	

	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()

def insertMYD04(query):
	
	org_folder = "SatResampMYD04"
	resample_folder= "SatResampMYD04_Daily"
	aod_command = "raster2pgsql -a -f rasref10 -F {0} apom.satresampmyd04_daily"

	con = None
	try:
		con = psycopg2.connect(host=constant.host, database = constant.database, user= constant.user, password=constant.password) 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		
		#cur.execute("SELECT filename, path, ST_AsText(corner) as corner FROM satorgmod04")
		cur.execute(query)
		rows = cur.fetchall()
		
		for row in rows:
			aqstime=row["aqstime"]
			type = row["type"]
			collection = row["collection"]
			path = row["filepath"].strip()
			filename = row["ju_date"].strip()
			
			
			path = os.path.join(constant.folder_path, path)
			
			resamp_path=path.replace(org_folder, resample_folder)
			

			# resampled file path
			aod_file = os.path.join(resamp_path,filename+"_CB_10km.tif")
			if os.path.isfile(aod_file):
				aod_script=os.popen(aod_command.format(aod_file)).read()
				first_index = aod_script.find("('")
				last_index = aod_script.find("':")
				aod_ref=aod_script[first_index+2:last_index]
				aod_insert = insert_daily_query.format("apom.satresampmyd04_daily",aqstime,aod_ref,filename+"_CB_10km.tif",resamp_path,1,type,collection)
				#insert_daily_query="INSERT INTO {0}(aqstime, rasref10, filename, filepath, projection,type, collection) VALUES ('{1}'::timestamp, '{2}'::raster, '{3}', '{4}', {5}, {6}, {7})" 

				cur.execute(aod_insert)
				cur.execute('COMMIT')
				
		

	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()
def insertMOD04(query):
	
	org_folder = "SatResampMOD04"
	resample_folder= "SatResampMOD04_Daily"
	aod_command = "raster2pgsql -a -f rasref10 -F {0} apom.satresampmod04_daily"

	con = None
	try:
		con = psycopg2.connect(host=constant.host, database = constant.database, user= constant.user, password=constant.password) 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		
		#cur.execute("SELECT filename, path, ST_AsText(corner) as corner FROM satorgmod04")
		cur.execute(query)
		rows = cur.fetchall()
		
		for row in rows:
			aqstime=row["aqstime"]
			type = row["type"]
			collection = row["collection"]
			path = row["filepath"].strip()
			filename = row["ju_date"].strip()
			
			
			path = os.path.join(constant.folder_path, path)
			
			resamp_path=path.replace(org_folder, resample_folder)
			

			# resampled file path
			aod_file = os.path.join(resamp_path,filename+"_DT_10km.tif")
			if os.path.isfile(aod_file):
				aod_script=os.popen(aod_command.format(aod_file)).read()
				first_index = aod_script.find("('")
				last_index = aod_script.find("':")
				aod_ref=aod_script[first_index+2:last_index]
				aod_insert = insert_daily_query.format("apom.satresampmod04_daily",aqstime,aod_ref,filename+"_DT_10km.tif",resamp_path,1,type,collection)
				#insert_daily_query="INSERT INTO {0}(aqstime, rasref10, filename, filepath, projection,type, collection) VALUES ('{1}'::timestamp, '{2}'::raster, '{3}', '{4}', {5}, {6}, {7})" 
				print(aod_ref)
				#cur.execute(aod_insert)
				#cur.execute('COMMIT')
				
		

	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()
			
def insertAll04(query):
	
	org_folder = "SatResampAll04"
	resample_folder= "SatResampAll04_Daily"
	aod_command = "raster2pgsql -a -f rasref10 -F {0} apom.satresampall04_daily"
	basepath="/var/www/html/fimo/apom/Resample/SatResampAll04_Daily/"
	con = None
	
	for fullPath in glob.iglob("/var/www/html/fimo/apom/Resample/SatResampAll04_Daily/*/*/*.tif"):
		filename=fullPath[67:74]
		filepath=fullPath[14:67]
		try:
			con = psycopg2.connect(host=constant.host, database = constant.database, user= constant.user, password=constant.password) 
			cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
			cur.execute(aqstime_query.format("apom.satresampmod04_daily",filename))
			row = cur.fetchone()
			if(row<=0):
				cur.execute(aqstime_query.format("apom.satresampmyd04_daily",filename))
				row = cur.fetchone()
			aqstime=row["aqstime"]
			aod_script=os.popen(aod_command.format(fullPath)).read()
			first_index = aod_script.find("('")
			last_index = aod_script.find("':")
			aod_ref=aod_script[first_index+2:last_index]
			aod_insert = insert_daily_query.format("apom.satresampall04_daily",aqstime,aod_ref,filename+"_DT_10km.tif",filepath,1,1,006)
				#insert_daily_query="INSERT INTO {0}(aqstime, rasref10, filename, filepath, projection,type, collection) VALUES ('{1}'::timestamp, '{2}'::raster, '{3}', '{4}', {5}, {6}, {7})" 

			cur.execute(aod_insert)
			cur.execute('COMMIT')
			

		except psycopg2.DatabaseError, e:
			print 'Error %s' % e    
			sys.exit(1)
		finally:
			if con:
				con.close()
def insertAll07(query):
	
	temp3_format = "_T_3km.tif"
	press3_format = "_P_3km.tif"
	temp6_format = "_T_6km.tif"
	press6_format = "_P_6km.tif"
	temp10_format = "_T_10km.tif"
	press10_format = "_P_10km.tif"
	basepath="/var/www/html/fimo/apom/Resample/SatResampAll07_Daily/*/*/*_T_10km.tif"
	raster2sql_command = "raster2pgsql -a -f rasref -F {0} apom.satresampmod07temperature_daily"
	
	con = None
	
	for fullPath in glob.iglob(basepath):
		filename=fullPath[67:74]
		filepath=fullPath[14:67]
		
		temp10_file=fullPath
		temp3_file = fullPath.replace(temp10_format,temp3_format)
		temp6_file = fullPath.replace(temp10_format,temp6_format)
		
		press3_file = fullPath.replace(temp10_format,press3_format)
		press6_file = fullPath.replace(temp10_format,press6_format)
		press10_file = fullPath.replace(temp10_format,press10_format)
		
		try:
			con = psycopg2.connect(host=constant.host, database = constant.database, user= constant.user, password=constant.password) 
			cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
			cur.execute(aqstime_query.format("apom.satresampmod07temperature_daily",filename))
			row = cur.fetchone()
			if(row<=0):
				cur.execute(aqstime_query.format("apom.satresampmyd07temperature_daily",filename))
				row = cur.fetchone()
			aqstime=row["aqstime"]
			if (os.path.isfile(temp3_file) and os.path.isfile(temp6_file) and os.path.isfile(temp10_file)):
				temp3_script=os.popen(raster2sql_command.format(temp3_file)).read()
				first_index = temp3_script.find("('")
				last_index = temp3_script.find("':")
				temp3_ref=temp3_script[first_index+2:last_index]
			
				temp6_script=os.popen(raster2sql_command.format(temp6_file)).read()
				first_index = temp6_script.find("('")
				last_index = temp6_script.find("':")
				temp6_ref=temp6_script[first_index+2:last_index]
			
				temp10_script=os.popen(raster2sql_command.format(temp10_file)).read()
				first_index = temp10_script.find("('")
				last_index = temp10_script.find("':")
				temp10_ref=temp10_script[first_index+2:last_index]
			
				
				insert_temp = insert_query.format("apom.satresampall07temperature_daily",aqstime,temp3_ref,filename,filepath)				
				cur.execute(insert_temp)
				cur.execute('COMMIT')
				
				update_temp=update_query.format("apom.satresampall07temperature_daily",temp6_ref,temp10_ref,filename)
				cur.execute(update_temp)
				cur.execute('COMMIT')
				
			if (os.path.isfile(temp3_file) and os.path.isfile(temp6_file) and os.path.isfile(temp10_file)):
				press3_script=os.popen(raster2sql_command.format(press3_file)).read()
				first_index = press3_script.find("('")
				last_index = press3_script.find("':")
				press3_ref=press3_script[first_index+2:last_index]
				
				press6_script=os.popen(raster2sql_command.format(press6_file)).read()	
				first_index = press6_script.find("('")
				last_index = press6_script.find("':")
				press6_ref=press6_script[first_index+2:last_index]
					
				press10_script=os.popen(raster2sql_command.format(press10_file)).read()	
				first_index = press10_script.find("('")
				last_index  = press10_script.find("':")
				press10_ref = press10_script[first_index+2:last_index]
			
				
				insert_press = insert_query.format("apom.satresampall07pressure_daily",aqstime,press3_ref,filename,filepath)
				cur.execute(insert_press)
				cur.execute('COMMIT')
				update_press = update_query.format("apom.satresampall07pressure_daily",press6_ref,press10_ref,filename)
				cur.execute(update_press)
				cur.execute('COMMIT')
			
		except psycopg2.DatabaseError, e:
			print 'Error %s' % e    
			sys.exit(1)
		finally:
			if con:
				con.close()
				
		
	
if __name__ == '__main__':
	insertMOD04(mod04_query.format("apom.satresampmod04"))
	print "MOD04 done"
	#insertMOD04(mod04_query.format("apom.satresampmyd04"))
	#print "MYD04 done"
	#insertMYD07(myd07_query)
	#print "MYD07 done"
	#insertMOD07(mod04_query.format("apom.satresampmod07temperature"))
	#print "MOD07 done"
	#insertMYD07(mod04_query.format("apom.satresampmyd07temperature"))
	#print "MOD07 done"
	#insertAll04(mod04_query.format("apom.satresampall04"))
	#print "All04 done"
	insertAll07(mod04_query.format("apom.satresampall04"))
	print "All07 done"
	
